<?php
class AgentController extends Zend_Controller_Action
{
	/*
	 * Send link action
	 */
	function sendlinkAction(){
		$this->view->title = "Send OTA link";

		// get registry variables
		$registry = Zend_Registry::getInstance();
		$others = $registry->get('others_config');
		$translator = $registry->get('Zend_Translate');
		$agent_config = $registry->get('agent_config');
		$others_config = $registry->get('others_config');

		//get current user information
		$auth = Zend_Auth::getInstance();
		$user = $auth->getIdentity();

		// init form
		$form = new SendOTALinkForm();
		$this->view->form = $form;

		// get all the related service
		$userservices = new UserService();
		$select = $userservices->getAdapter()->select()
											->from(array('us' => 'vmg_user_service'))
											->joinleft(array('s' => 'vmg_service'),
														'us.service_id = s.id',
														array("code"))
											->where('us.agent_id = ?', $user->getid());
		$rows = $userservices->getAdapter()->fetchAll($select);
		if(count($rows) > 0){
			foreach($rows as $row){
				$form->getElement('serviceCodeSendotalinkForm')->addMultiOption($row['agent_service_code'], $row['code'] );
			}
		}

		// process post request
		if($this->_request->isPost()){
			$formData = $this->_request->getPost();
			if($form->isValid($formData)){ // form input is valid, send it
				if (!$form->getElement('csvSendotalinkForm')->receive()) {
			        $form->populate($formData);
			        $form->getElement('csvSendotalinkForm')->setErrors(array($translator->_('Can not upload the file')));
			    }else{
					$cellphones = array();
					if($form->getValue('phoneSendotalinkForm') !== ""){
						$cellphones = explode("\r\n", $form->getValue('phoneSendotalinkForm'));
					}
					if($form->getValue('csvSendotalinkForm')){
						$fullFilePath = $form->csvSendotalinkForm->getFileName();
						$fp = fopen($fullFilePath, 'r');
						if($form->getValue('csvDelimiterSendotalinkForm') !== ""){
							$delimiter = $form->getValue('csvDelimiterSendotalinkForm');
						}else{
							$delimiter = ",";
						}
						$csv_data = fgetcsv($fp, null, $delimiter);
						fclose($fp);
						if(count($csv_data) > 0){
							foreach($csv_data as $phone){
								if(preg_match('/^[0-9]+$/', $phone) >= 1 && array_search($phone, $cellphones) === FALSE){
									array_push($cellphones, $phone);
								}
							}
						}
					}
					$i = 0;
					$results = array();
					if(count($cellphones) > 0){
						$wsdl = $others->vmg_wsdl;
						$soap_client = new Vmg_Soap_OTAService($wsdl);
						$otaRequest = new SendOtaDownloadLink();
						$otaRequest->channelCode = $form->getValue('serviceCodeSendotalinkForm');
						foreach($cellphones as $cellphone){
							$i++;
							$cellphone = trim($cellphone, '0');
							$otaRequest->msisdn = '+84' . $cellphone;
							if(strlen($cellphone) < ($others_config->min_cellphone_length -1) || strlen($cellphone) > ($others_config->max_cellphone_length -1)){
								$results[$i]["code"] = -1;
								$results[$i]["message"] = $cellphone . ": " .  $translator->_("Phone number is too long or is invalid");
								continue;
							}
							//var_dump($otaRequest);
							try{
								$otaResponse = $soap_client->SendOtaDownloadLink($otaRequest);
								$xml = new SimpleXMLElement($otaResponse->SendOtaDownloadLinkResult->any);
								if($xml['code'] != 0){
									$results[$i]["message"] = $cellphone . ": " . $translator->translate((string)$xml['message']);
								}else{
									$results[$i]["message"] = $cellphone . ": " . $translator->translate("Link sent successfully");
								}
								$results[$i]["code"] = $xml['code'];
							}catch(SoapFault $e){
								$error = $e->getMessage();
								$errno = $e->getCode();
								$results[$i]["code"] = $errno;
								$results[$i]["message"] = $cellphone . ": " . $translator->_($error);
							};

							// more than max number of phones
							if($i >= $agent_config->max_send_phones){
								break;
							}
						}
					}
					$this->view->result = $results;
			    }
			}else{
				$form->populate($formData);
			}
		}
	}

	/*
	 *  Operating report action
	 *
	 */
	function operatingreportAction(){
		// set title
		$this->view->title = "Operating report";

		// get registry value
		$registry = Zend_Registry::getInstance();
		$access_levels = $registry->get('access_level_config');
		$paging_config = $registry->get('paging_config');
		$reporting_config = $registry->get('reporting_config');
		$translator = $registry->get('Zend_Translate');

		// get agent information
		$auth = Zend_Auth::getInstance();
		$agent = $auth->getIdentity();
		$agent_id = (int)$agent->getId();
		$agent_name = $agent->getName();
		$this->view->agent_name = $agent_name;

		// create form
		$reportForm = new ReportForm();
		$exportButton = $reportForm->getElement('exportReportForm');

		// remove the salesman and agent selection controls
		$reportForm->removeElement('salesmanReportForm');
		$reportForm->removeElement('agentReportForm');
		$show_flag = true;
		if($this->_request->isPost()){ // get report conditions when user post information
			$formData = $this->_request->getPost();
			if($reportForm->isValid($formData)){
				$startHour = mysql_escape_string($reportForm->getValue('startHourReportForm'));
				$startDay = mysql_escape_string($reportForm->getValue('startDayReportForm'));
				$endHour = mysql_escape_string($reportForm->getValue('endHourReportForm'));
				$endDay = mysql_escape_string($reportForm->getValue('endDayReportForm'));
				$month = mysql_escape_string($reportForm->getValue('monthReportForm'));
				$year = mysql_escape_string($reportForm->getValue('yearReportForm'));
				$page = 1;
				if($reportForm->getValue('exportReportForm') != NULL){
					$show_flag = false;
				}
			}
		}else{ // get report conditions when users naviagate through the pagination control
			$startHour = mysql_escape_string($this->_request->getParam('startHourReportForm'));
			$startDay = mysql_escape_string($this->_request->getParam('startDayReportForm'));
			$endHour = mysql_escape_string($this->_request->getParam('endHourReportForm'));
			$endDay = mysql_escape_string($this->_request->getParam('endDayReportForm'));
			$month = mysql_escape_string($this->_request->getParam('monthReportForm'));
			$year = mysql_escape_string($this->_request->getParam('yearReportForm'));
			// fetch rows to show
			$page = $this->_getParam('page',1);
		}
		$this->view->page = $page;
		$this->view->itemsPerPage = $paging_config->items;
		if(isset($year) && $year > 0){
			$month += 1 ;
			if(strlen($month) == 1) $month = '0' . $month;
			if(strlen($startDay) == 1) $startDay = '0' . $startDay;
			if(strlen($endDay) == 1) $endDay = '0' . $endDay;
			if(strlen($startHour) == 1) $startHour = '0' . $startHour;
			if(strlen($endHour) == 1) $endHour = '0' . $endHour;
			$this->view->params = array(
				'startHourReportForm' => $startHour,
				'startDayReportForm' => $startDay,
				'endHourReportForm' => $endHour,
				'endDayReportForm' => $endDay,
				'monthReportForm' => $month-1,
				'yearReportForm' => $year
			);
			$startDatetime = $year . "-" . $month . "-" . $startDay . " " . $startHour . ":00:00";
			$endDatetime = $year . "-" . $month . "-" . $endDay . " " . $endHour . ":00:00";
			$users = new Users();
			$billing_table = 'vmg_billing_count' . $year . $month;
			$select = $users->getAdapter()->select()
								->from(array( 'bt' => $billing_table))
								->where('bt.msisdn in (SELECT su.msisdn FROM `vmg_service_user` AS su
	LEFT JOIN `vmg_user_service` AS us ON su.service_channel_id = us.id)')
								->where('bt.date BETWEEN CAST(\'' . $startDatetime . '\' AS DATETIME) AND CAST(\'' . $endDatetime . '\' AS DATETIME)')
								->joinleft(array('su' => 'vmg_service_user'),  '(bt.msisdn = su.msisdn)', array())
								->joinleft(array('us' => 'vmg_user_service'),  'su.service_channel_id = us.id', array())
								->joinleft(array('u' => 'vmg_user'), 'u.id = us.agent_id', array(''))
								->where('u.id = ?', $agent_id)
								->where('su.activ_date in (SELECT max(activ_date) FROM vmg_service_user WHERE msisdn = bt.msisdn and bt.date > activ_date)')
								->order('bt.date DESC')

								;

			$select_sum = $users->getAdapter()->select()
								->from(array( 'bt' => $billing_table), array( "sum(bt.cdr_count) as num_trans", "sum(money) as total_money"))
								->where('bt.msisdn in (SELECT su.msisdn FROM `vmg_service_user` AS su
	LEFT JOIN `vmg_user_service` AS us ON su.service_channel_id = us.id)')
								->where('bt.date BETWEEN CAST(\'' . $startDatetime . '\' AS DATETIME) AND CAST(\'' . $endDatetime . '\' AS DATETIME)')
								->joinleft(array('su' => 'vmg_service_user'),  '(bt.msisdn = su.msisdn)', array())
								->joinleft(array('us' => 'vmg_user_service'),  'su.service_channel_id = us.id', array())
								->joinleft(array('u' => 'vmg_user'), 'u.id = us.agent_id', array())
								->where('u.id = ?', $agent_id)
								->where('su.activ_date in (SELECT max(activ_date) FROM vmg_service_user WHERE msisdn = bt.msisdn and bt.date > activ_date)')
								;

			$select_count = $users->getAdapter()->select()
								->from(array( 'bt' => $billing_table), array( "count(bt.cdr_count) as num_rows"))
								->where('bt.msisdn in (SELECT su.msisdn FROM `vmg_service_user` AS su
	LEFT JOIN `vmg_user_service` AS us ON su.service_channel_id = us.id)')
								->where('bt.date BETWEEN CAST(\'' . $startDatetime . '\' AS DATETIME) AND CAST(\'' . $endDatetime . '\' AS DATETIME)')
								->joinleft(array('su' => 'vmg_service_user'),  '(bt.msisdn = su.msisdn)', array())
								->joinleft(array('us' => 'vmg_user_service'),  'su.service_channel_id = us.id', array())
								->joinleft(array('u' => 'vmg_user'), 'u.id = us.agent_id', array(''))
								->where('u.id = ?', $agent_id)
								->where('su.activ_date in (SELECT max(activ_date) FROM vmg_service_user WHERE msisdn = bt.msisdn and bt.date > activ_date)')
								;

			if($show_flag){	// show the result
				$row = $users->getAdapter()->fetchRow($select_sum);
				$total_amount = $row["total_money"];
				$num_trans = $row["num_trans"];
				$this->view->total_amount = $total_amount;
				$this->view->num_trans = $num_trans;
				// init paginator object
				$paginator = Zend_Paginator::factory($select);
				$paginator->setItemCountPerPage($paging_config->items);
				$paginator->setCurrentPageNumber($page);
				$this->view->paginator=$paginator;
				$reportForm->populate($this->view->params);

			}else{ // export all result to a excel file
				//open file
				$filename = uniqid() . ".csv";
				 header('Content-Type: text/csv;');
                 header("Content-Disposition: attachment; filename=$filename");
					$row = $users->getAdapter()->fetchRow($select_count);
					$num_records = $row["num_rows"];
					$select_limit = $users->getAdapter()->select()
								->from(array( 'bt' => $billing_table))
								->where('bt.msisdn in (SELECT su.msisdn FROM `vmg_service_user` AS su
	LEFT JOIN `vmg_user_service` AS us ON su.service_channel_id = us.id)')
								->where('bt.date BETWEEN CAST(\'' . $startDatetime . '\' AS DATETIME) AND CAST(\'' . $endDatetime . '\' AS DATETIME)')
								->joinleft(array('su' => 'vmg_service_user'),  '(bt.msisdn = su.msisdn)', array())
								->joinleft(array('us' => 'vmg_user_service'),  'su.service_channel_id = us.id', array())
								->joinleft(array('u' => 'vmg_user'), 'u.id = us.agent_id', array(''))
								->where('u.id = ?', $agent_id)
								->where('su.activ_date in (SELECT max(activ_date) FROM vmg_service_user WHERE msisdn = bt.msisdn and bt.date > activ_date)')
								->order('bt.date DESC')
								;
					$i = $num_records/1000;
				 	$tmp = array(
						$translator->_("Ngay"),
				 		$translator->_("Gio"),
				 		$translator->_("Thue bao"),
						$translator->_("Dau so"),
						$translator->_("Phi dich vu")
					);
					echo "Nam, " . $year;
					echo "\n";
					echo "Thang, " . $month;
					echo "\n";
					echo implode("," , $tmp) . "\n";
					for($j = 0; $j <= $i; $j++){
						$select_limit->limit(1000, $j * 1000);
						$rows = $users->getAdapter()->fetchAll($select_limit);

						foreach($rows as $row){
							echo substr($row['date'], 8,2) . ",";
							echo substr($row['date'], 11, 2) . ",";
							$row['msisdn'] = str_replace("+84", "'0", $row['msisdn']);
							echo $row['msisdn'] . ",";
							echo $row['short_code'] . ",";
							echo $row['money'] . ",";
							echo  "\n";
						}
					}
					exit;
				}
			}
		// attach form to view
		$exportButton->setValue('Export data');
		$reportForm->getElement('submitReportForm')->setValue("Show report");
		$this->view->form = $reportForm;
	}

	/**
	 * Customer development report action
	 *
	 */
	function customerreportAction(){
		// set title
		$this->view->title = "Customer development report for agent";

		// get registry value
		$registry = Zend_Registry::getInstance();
		$access_levels = $registry->get('access_level_config');
		$paging_config = $registry->get('paging_config');
		$reporting_config = $registry->get('reporting_config');
		$translator = $registry->get('Zend_Translate');

		// get agent information
		$auth = Zend_Auth::getInstance();
		$agent = $auth->getIdentity();
		$agent_id = (int)$agent->getId();
		$agent_name = $agent->getName();

		// create form
		$reportForm = new ReportForm();
		$exportButton = $reportForm->getElement('exportReportForm');

		// remove the salesman and agent selection controls
		$reportForm->removeElement('salesmanReportForm');
		$reportForm->removeElement('agentReportForm');
		$show_flag = true;
		if($this->_request->isPost()){ // get report conditions when user post information
			$formData = $this->_request->getPost();
			if($reportForm->isValid($formData)){
				$startHour = mysql_escape_string($reportForm->getValue('startHourReportForm'));
				$startDay = mysql_escape_string($reportForm->getValue('startDayReportForm'));
				$endHour = mysql_escape_string($reportForm->getValue('endHourReportForm'));
				$endDay = mysql_escape_string($reportForm->getValue('endDayReportForm'));
				$month = mysql_escape_string($reportForm->getValue('monthReportForm'));
				$year = mysql_escape_string($reportForm->getValue('yearReportForm'));
				$page = 1;
				if($reportForm->getValue('exportReportForm') != NULL){
					$show_flag = false;
				}
			}
		}else{ // get report conditions when users naviagate through the pagination control
			$startHour = mysql_escape_string($this->_request->getParam('startHourReportForm'));
			$startDay = mysql_escape_string($this->_request->getParam('startDayReportForm'));
			$endHour = mysql_escape_string($this->_request->getParam('endHourReportForm'));
			$endDay = mysql_escape_string($this->_request->getParam('endDayReportForm'));
			$month = mysql_escape_string($this->_request->getParam('monthReportForm'));
			$year = mysql_escape_string($this->_request->getParam('yearReportForm'));
			if($this->_request->getParam('agentReportForm')){
				$agent_id = (int)$this->_request->getParam('agentReportForm');
			}

			if($this->_request->getParam('agentNameReportForm')){
				$agent_name= (string)$this->_request->getParam('agentNameReportForm');
			}
			// fetch rows to show
			$page = $this->_getParam('page',1);
		}
		$this->view->page = $page;
		$this->view->itemsPerPage = $paging_config->items;
		if(isset($year) && $year > 0){
			$month += 1 ;
			if(strlen($month) == 1) $month = '0' . $month;
			if(strlen($startDay) == 1) $startDay = '0' . $startDay;
			if(strlen($endDay) == 1) $endDay = '0' . $endDay;
			if(strlen($startHour) == 1) $startHour = '0' . $startHour;
			if(strlen($endHour) == 1) $endHour = '0' . $endHour;
			$this->view->params = array(
				'startHourReportForm' => $startHour,
				'startDayReportForm' => $startDay,
				'endHourReportForm' => $endHour,
				'endDayReportForm' => $endDay,
				'monthReportForm' => $month-1,
				'yearReportForm' => $year
			);
			$this->view->agent_name = $agent_name;
			$startDatetime = $year . "-" . $month . "-" . $startDay . " " . $startHour . ":00:00";
			$endDatetime = $year . "-" . $month . "-" . $endDay . " " . $endHour . ":00:00";
			$users = new Users();
			$select = $users->getAdapter()->select()
								->from(array('su' => 'vmg_service_user'))
								->where('activ_date BETWEEN CAST(\'' . $startDatetime . '\' AS DATETIME) AND CAST(\'' . $endDatetime . '\' AS DATETIME)')
								->joinLeft(array('us' => 'vmg_user_service'), 'su.service_channel_id = us.id')
								->joinLeft(array('u' => 'vmg_user'), 'u.id = us.agent_id')
								->where('u.id = ?', $agent_id)
								->order(array('activ_date DESC'))
								;
			$select_count = $users->getAdapter()->select()
								->from(array('su' => 'vmg_service_user'), array('count(*) as num_rows'))
								->where('activ_date BETWEEN CAST(\'' . $startDatetime . '\' AS DATETIME) AND CAST(\'' . $endDatetime . '\' AS DATETIME)')
								->joinLeft(array('us' => 'vmg_user_service'), 'su.service_channel_id = us.id', array())
								->joinLeft(array('u' => 'vmg_user'), 'u.id = us.agent_id', array())
								->where('u.id = ?', $agent_id)
								;

			if($show_flag){	// show the result
				$row = $users->getAdapter()->fetchRow($select_count);
				$num_customers = $row["num_rows"];
				$this->view->num_customers = $num_customers;
				// init paginator object
				$paginator = Zend_Paginator::factory($select);
				$paginator->setItemCountPerPage($paging_config->items);
				$paginator->setCurrentPageNumber($page);
				$this->view->paginator=$paginator;
				$reportForm->populate($this->view->params);
			}else{ // export all result to a excel file
				//open file
				$filename = uniqid() . ".csv";
				 header('Content-Type: text/csv;');
                 header("Content-Disposition: attachment; filename=$filename");
					$row = $users->getAdapter()->fetchRow($select_count);
					$num_records = $row["num_rows"];
					$select_limit = $users->getAdapter()->select()
									->from(array('su' => 'vmg_service_user'))
									->where('activ_date BETWEEN CAST(\'' . $startDatetime . '\' AS DATETIME) AND CAST(\'' . $endDatetime . '\' AS DATETIME)')
									->joinLeft(array('us' => 'vmg_user_service'), 'su.service_channel_id = us.id')
									->joinLeft(array('u' => 'vmg_user'), 'u.id = us.agent_id')
									->where('u.id = ?', $agent_id)
									->order(array('activ_date DESC'))
									;
					echo "Nam, " . $year;
					echo "\n";
					echo "Thang, " . $month;
					echo "\n";
					$i = $num_records/1000;
				 	$tmp = array(
						$translator->_("Ngay kich hoat"),
				 		$translator->_("Gio kich hoat"),
				 		$translator->_("Thue bao"),
						$translator->_("Hang SX"),
						$translator->_("Loai DT"),
						$translator->_("Chu thue bao"),
						$translator->_("Thanh pho"),
						$translator->_("Dia chi"),
						$translator->_("Email")
					);
					echo implode("," , $tmp) . "\n";
					for($j = 0; $j <= $i; $j++){
						$select_limit->limit(1000, $j * 1000);
						$rows = $users->getAdapter()->fetchAll($select_limit);

						foreach($rows as $row){
							echo substr($row['activ_date'], 8,2) . ",";
							echo substr($row['activ_date'], 11, 2) . ",";
							$row['msisdn'] = str_replace("+84", "'0", $row['msisdn']);
							echo $row['msisdn'] . ",";
							echo str_replace(",", "-",$row['device_brand']) . ",";
							echo $row['device_model'] . ",";
							echo $row['fullname'] . ",";
							echo str_replace(",", "-",$row['city']) . ",";
							echo str_replace(",", "-",$row['address']) . ",";
							echo $row['email'];
							echo  "\n";
						}
					}
					exit;
				}
			}
		// attach form to view
		$exportButton->setValue('Export data');
		$reportForm->getElement('submitReportForm')->setValue("Show report");
		$this->view->form = $reportForm;
	}

	function customersearchAction(){
		// set title
		$this->view->title = "Search customers";
		$searchForm = new CustomerSearchForm();
		$this->view->form = $searchForm;

		// get registry value
		$registry = Zend_Registry::getInstance();
		$access_levels = $registry->get('access_level_config');
		$paging_config = $registry->get('paging_config');
		$reporting_config = $registry->get('reporting_config');
		$translator = $registry->get('Zend_Translate');

		// get agent information
		$auth = Zend_Auth::getInstance();
		$agent = $auth->getIdentity();
		$agent_id = (int)$agent->getId();
		$agent_name = $agent->getName();

		$page = 0;
		$cellphone = '';
		if($this->_request->isPost()){ // get search conditions when user post information
			$formData = $this->_request->getPost();
			if($searchForm->isValid($formData)){
				$cellphone = mysql_escape_string($searchForm->getValue('mobileCustomerSearchForm'));
				$page = 1;
			}else{
				$searchForm->populate($formData);
			}
		}else{ // get search conditions when users naviagate through the pagination control
			$cellphone = mysql_escape_string( $this->_request->getParam('mobileCustomerSearchForm'));
			// fetch rows to show
			$page = $this->_getParam('page',0);
		}

		if($page > 0){
			$this->view->params = array('mobileCustomerSearchForm'=> $cellphone);
			// init users database object
			$serviceusers = new ServiceUser();
			$select = $serviceusers->getAdapter()->select()
									->from(array('su'=> 'vmg_service_user'))
						            ->joinLeft(array('us' => 'vmg_user_service'), 'su.service_channel_id = us.id')
									->joinLeft(array('u' => 'vmg_user'), 'u.id = us.agent_id', array('u.name as agent_name'))
									->where('su.msisdn like \'%' . $cellphone . '%\'')
									;
			if(!(strtolower($agent->getType()) === "special agent")){
				$select = $select->where('u.id = ?', $agent_id);
			}else{
				$this->view->agent_type_name = "special agent";
			}
			// init paginator object
			$paginator = Zend_Paginator::factory($select);
			$paginator->setItemCountPerPage($paging_config->items);
			$paginator->setCurrentPageNumber($page);

			// set paginator
			$this->view->paginator=$paginator;
		}

		// init search form
		$searchForm->getElement('mobileCustomerSearchForm')->setValue($cellphone);
		$searchForm->getElement('submitCustomerSearchForm')->setValue("Search");
		$this->view->form = $searchForm;
	}
}

?>